--    Author    : MAYANTING
--    Name      : ADM.RPT_SUST_DLMG_TERM_SCALE_LOAB.HQL
--    Functions : 表5：贷款余额企业规模期限结构表
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2018-06-11  MAYANTING           1.CREATE THE PROCEDURE
--
INSERT OVERWRITE TABLE ADM.RPT_SUST_DLMG_TERM_SCALE_LOAB PARTITION (DATA_DATE = '20180331')
    SELECT  XY.FIN_ORG_DIST
           ,XY.CCY
           ,XY.ENTERPRISE_SCALE
           ,XY.SCALE_DESC
           ,SUM(XY.BAL_0_6M     )/100000000
           ,SUM(XY.WSUM_0_6M    )/100000000
           ,SUM(XY.BAL_6_12M    )/100000000
           ,SUM(XY.WSUM_6_12M   )/100000000
           ,SUM(XY.BAL_1_3Y     )/100000000
           ,SUM(XY.WSUM_1_3Y    )/100000000
           ,SUM(XY.BAL_3_5Y     )/100000000
           ,SUM(XY.WSUM_3_5Y    )/100000000
           ,SUM(XY.BAL_5_10Y    )/100000000
           ,SUM(XY.WSUM_5_10Y   )/100000000
           ,SUM(XY.BAL_10_999Y  )/100000000
           ,SUM(XY.WSUM_10_999Y )/100000000
           ,SUM(XY.BAL_ALL      )/100000000
           ,SUM(XY.WSUM_ALL     )/100000000
    FROM (
        -- 大型企业 中型企业 小型企业 微型企业
        SELECT
        AREA.AREA_CODE_4                                                          AS FIN_ORG_DIST
        ,YE.CCY                                                                   AS CCY
        ,YE.ENTERPRISE_SCALE                                                      AS ENTERPRISE_SCALE    -- 企业规模
        ,CASE WHEN YE.ENTERPRISE_SCALE='CS01' THEN '大型企业'
              WHEN YE.ENTERPRISE_SCALE='CS02' THEN '中型企业'
              WHEN YE.ENTERPRISE_SCALE='CS03' THEN '小型企业'
              WHEN YE.ENTERPRISE_SCALE='CS04' THEN '微型企业'
        END                                                                       AS SCALE_DESC
        ,COALESCE(CASE WHEN YE.TERM_CODE ='1' THEN YE.ACTUAL_BAL ELSE 0 END,0)    AS BAL_0_6M            --6个月（含）以内-贷款余额
        ,COALESCE(CASE WHEN YE.TERM_CODE ='1' THEN YE.WSUM_BAL   ELSE 0 END,0)    AS WSUM_0_6M           --6个月（含）以内-加权金额
        ,COALESCE(CASE WHEN YE.TERM_CODE ='2' THEN YE.ACTUAL_BAL ELSE 0 END,0)    AS BAL_6_12M           --6个月至1年（含）-贷款余额
        ,COALESCE(CASE WHEN YE.TERM_CODE ='2' THEN YE.WSUM_BAL   ELSE 0 END,0)    AS WSUM_6_12M          --6个月至1年（含）-加权金额
        ,COALESCE(CASE WHEN YE.TERM_CODE ='3' THEN YE.ACTUAL_BAL ELSE 0 END,0)    AS BAL_1_3Y            --1至3年（含）-贷款余额
        ,COALESCE(CASE WHEN YE.TERM_CODE ='3' THEN YE.WSUM_BAL   ELSE 0 END,0)    AS WSUM_1_3Y           --1至3年（含）-加权金额
        ,COALESCE(CASE WHEN YE.TERM_CODE ='4' THEN YE.ACTUAL_BAL ELSE 0 END,0)    AS BAL_3_5Y            --3至5年（含）-贷款余额
        ,COALESCE(CASE WHEN YE.TERM_CODE ='4' THEN YE.WSUM_BAL   ELSE 0 END,0)    AS WSUM_3_5Y           --3至5年（含）-加权金额
        ,COALESCE(CASE WHEN YE.TERM_CODE ='5' THEN YE.ACTUAL_BAL ELSE 0 END,0)    AS BAL_5_10Y           --5至10年（含）-贷款余额
        ,COALESCE(CASE WHEN YE.TERM_CODE ='5' THEN YE.WSUM_BAL   ELSE 0 END,0)    AS WSUM_5_10Y          --5至10年（含）-加权金额
        ,COALESCE(CASE WHEN YE.TERM_CODE ='6' THEN YE.ACTUAL_BAL ELSE 0 END,0)    AS BAL_10_999Y         --10年以上-贷款余额
        ,COALESCE(CASE WHEN YE.TERM_CODE ='6' THEN YE.WSUM_BAL   ELSE 0 END,0)    AS WSUM_10_999Y        --10年以上-加权金额
        ,COALESCE(YE.ACTUAL_BAL,0)                                                AS BAL_ALL             --所有期限-贷款余额
        ,COALESCE(YE.WSUM_BAL,0)                                                  AS WSUM_ALL            --所有期限-加权金额
        FROM (SELECT * FROM EDW.DS_LOAB_SUM WHERE ENTERPRISE_SCALE IN('CS01','CS02','CS03','CS04') AND DATA_DATE='20180331')YE
        -- 与地区表最细级关联
        LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON YE.FIN_ORG_NO=REAL.ORG_ID AND '20180331' BETWEEN REAL.START_DATE AND REAL.END_DATE
        LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '20180331' BETWEEN AREA.START_DATE AND AREA.END_DATE

        UNION ALL

        -- 合计
        SELECT
        AREA.AREA_CODE_4                                                              AS FIN_ORG_DIST
        ,LOAB.CCY                                                                     AS CCY
        ,'Z'                                                                          AS ENTERPRISE_SCALE    -- 企业规模
        ,'合计'                                                                       AS SCALE_DESC
        ,COALESCE(CASE WHEN LOAB.TERM_CODE ='1' THEN LOAB.ACTUAL_BAL ELSE 0 END,0)    AS BAL_0_6M            --6个月（含）以内-贷款余额
        ,COALESCE(CASE WHEN LOAB.TERM_CODE ='1' THEN LOAB.WSUM_BAL   ELSE 0 END,0)    AS WSUM_0_6M           --6个月（含）以内-加权金额
        ,COALESCE(CASE WHEN LOAB.TERM_CODE ='2' THEN LOAB.ACTUAL_BAL ELSE 0 END,0)    AS BAL_6_12M           --6个月至1年（含）-贷款余额
        ,COALESCE(CASE WHEN LOAB.TERM_CODE ='2' THEN LOAB.WSUM_BAL   ELSE 0 END,0)    AS WSUM_6_12M          --6个月至1年（含）-加权金额
        ,COALESCE(CASE WHEN LOAB.TERM_CODE ='3' THEN LOAB.ACTUAL_BAL ELSE 0 END,0)    AS BAL_1_3Y            --1至3年（含）-贷款余额
        ,COALESCE(CASE WHEN LOAB.TERM_CODE ='3' THEN LOAB.WSUM_BAL   ELSE 0 END,0)    AS WSUM_1_3Y           --1至3年（含）-加权金额
        ,COALESCE(CASE WHEN LOAB.TERM_CODE ='4' THEN LOAB.ACTUAL_BAL ELSE 0 END,0)    AS BAL_3_5Y            --3至5年（含）-贷款余额
        ,COALESCE(CASE WHEN LOAB.TERM_CODE ='4' THEN LOAB.WSUM_BAL   ELSE 0 END,0)    AS WSUM_3_5Y           --3至5年（含）-加权金额
        ,COALESCE(CASE WHEN LOAB.TERM_CODE ='5' THEN LOAB.ACTUAL_BAL ELSE 0 END,0)    AS BAL_5_10Y           --5至10年（含）-贷款余额
        ,COALESCE(CASE WHEN LOAB.TERM_CODE ='5' THEN LOAB.WSUM_BAL   ELSE 0 END,0)    AS WSUM_5_10Y          --5至10年（含）-加权金额
        ,COALESCE(CASE WHEN LOAB.TERM_CODE ='6' THEN LOAB.ACTUAL_BAL ELSE 0 END,0)    AS BAL_10_999Y         --10年以上-贷款余额
        ,COALESCE(CASE WHEN LOAB.TERM_CODE ='6' THEN LOAB.WSUM_BAL   ELSE 0 END,0)    AS WSUM_10_999Y        --10年以上-加权金额
        ,COALESCE(LOAB.ACTUAL_BAL,0)                                                  AS BAL_ALL             --所有期限-贷款余额
        ,COALESCE(LOAB.WSUM_BAL,0)                                                    AS WSUM_ALL            --所有期限-加权金额
        FROM (SELECT * FROM EDW.DS_LOAB_SUM WHERE ENTERPRISE_SCALE IN('CS01','CS02','CS03','CS04') AND DATA_DATE='20180331') LOAB
        -- 与地区表最细级关联
        LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON LOAB.FIN_ORG_NO=REAL.ORG_ID AND '20180331' BETWEEN REAL.START_DATE AND REAL.END_DATE
        LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '20180331' BETWEEN AREA.START_DATE AND AREA.END_DATE
        WHERE LOAB.DATA_DATE='20180331'

        UNION ALL

        -- 中小企业
        SELECT
        AREA.AREA_CODE_4                                                          AS FIN_ORG_DIST
        ,YE.CCY                                                                   AS CCY
        ,'CS234'                                                                   AS ENTERPRISE_SCALE    -- 企业规模
        ,'中小企业'                                                               AS SCALE_DESC
        ,COALESCE(CASE WHEN YE.TERM_CODE ='1' THEN YE.ACTUAL_BAL ELSE 0 END,0)    AS BAL_0_6M            --6个月（含）以内-贷款余额
        ,COALESCE(CASE WHEN YE.TERM_CODE ='1' THEN YE.WSUM_BAL   ELSE 0 END,0)    AS WSUM_0_6M           --6个月（含）以内-加权金额
        ,COALESCE(CASE WHEN YE.TERM_CODE ='2' THEN YE.ACTUAL_BAL ELSE 0 END,0)    AS BAL_6_12M           --6个月至1年（含）-贷款余额
        ,COALESCE(CASE WHEN YE.TERM_CODE ='2' THEN YE.WSUM_BAL   ELSE 0 END,0)    AS WSUM_6_12M          --6个月至1年（含）-加权金额
        ,COALESCE(CASE WHEN YE.TERM_CODE ='3' THEN YE.ACTUAL_BAL ELSE 0 END,0)    AS BAL_1_3Y            --1至3年（含）-贷款余额
        ,COALESCE(CASE WHEN YE.TERM_CODE ='3' THEN YE.WSUM_BAL   ELSE 0 END,0)    AS WSUM_1_3Y           --1至3年（含）-加权金额
        ,COALESCE(CASE WHEN YE.TERM_CODE ='4' THEN YE.ACTUAL_BAL ELSE 0 END,0)    AS BAL_3_5Y            --3至5年（含）-贷款余额
        ,COALESCE(CASE WHEN YE.TERM_CODE ='4' THEN YE.WSUM_BAL   ELSE 0 END,0)    AS WSUM_3_5Y           --3至5年（含）-加权金额
        ,COALESCE(CASE WHEN YE.TERM_CODE ='5' THEN YE.ACTUAL_BAL ELSE 0 END,0)    AS BAL_5_10Y           --5至10年（含）-贷款余额
        ,COALESCE(CASE WHEN YE.TERM_CODE ='5' THEN YE.WSUM_BAL   ELSE 0 END,0)    AS WSUM_5_10Y          --5至10年（含）-加权金额
        ,COALESCE(CASE WHEN YE.TERM_CODE ='6' THEN YE.ACTUAL_BAL ELSE 0 END,0)    AS BAL_10_999Y         --10年以上-贷款余额
        ,COALESCE(CASE WHEN YE.TERM_CODE ='6' THEN YE.WSUM_BAL   ELSE 0 END,0)    AS WSUM_10_999Y        --10年以上-加权金额
        ,COALESCE(YE.ACTUAL_BAL,0)                                                AS BAL_ALL             --所有期限-贷款余额
        ,COALESCE(YE.WSUM_BAL,0)                                                  AS WSUM_ALL            --所有期限-加权金额
        FROM (SELECT * FROM EDW.DS_LOAB_SUM WHERE ENTERPRISE_SCALE IN('CS02','CS03','CS04') AND DATA_DATE='20180331')YE
        -- 与地区表最细级关联
        LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON YE.FIN_ORG_NO=REAL.ORG_ID AND '20180331' BETWEEN REAL.START_DATE AND REAL.END_DATE
        LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '20180331' BETWEEN AREA.START_DATE AND AREA.END_DATE

        UNION ALL

        -- 小微企业
        SELECT
        AREA.AREA_CODE_4                                                          AS FIN_ORG_DIST
        ,YE.CCY                                                                   AS CCY
        ,'CS34'                                                                   AS ENTERPRISE_SCALE    -- 企业规模
        ,'小微企业'                                                               AS SCALE_DESC
        ,COALESCE(CASE WHEN YE.TERM_CODE ='1' THEN YE.ACTUAL_BAL ELSE 0 END,0)    AS BAL_0_6M            --6个月（含）以内-贷款余额
        ,COALESCE(CASE WHEN YE.TERM_CODE ='1' THEN YE.WSUM_BAL   ELSE 0 END,0)    AS WSUM_0_6M           --6个月（含）以内-加权金额
        ,COALESCE(CASE WHEN YE.TERM_CODE ='2' THEN YE.ACTUAL_BAL ELSE 0 END,0)    AS BAL_6_12M           --6个月至1年（含）-贷款余额
        ,COALESCE(CASE WHEN YE.TERM_CODE ='2' THEN YE.WSUM_BAL   ELSE 0 END,0)    AS WSUM_6_12M          --6个月至1年（含）-加权金额
        ,COALESCE(CASE WHEN YE.TERM_CODE ='3' THEN YE.ACTUAL_BAL ELSE 0 END,0)    AS BAL_1_3Y            --1至3年（含）-贷款余额
        ,COALESCE(CASE WHEN YE.TERM_CODE ='3' THEN YE.WSUM_BAL   ELSE 0 END,0)    AS WSUM_1_3Y           --1至3年（含）-加权金额
        ,COALESCE(CASE WHEN YE.TERM_CODE ='4' THEN YE.ACTUAL_BAL ELSE 0 END,0)    AS BAL_3_5Y            --3至5年（含）-贷款余额
        ,COALESCE(CASE WHEN YE.TERM_CODE ='4' THEN YE.WSUM_BAL   ELSE 0 END,0)    AS WSUM_3_5Y           --3至5年（含）-加权金额
        ,COALESCE(CASE WHEN YE.TERM_CODE ='5' THEN YE.ACTUAL_BAL ELSE 0 END,0)    AS BAL_5_10Y           --5至10年（含）-贷款余额
        ,COALESCE(CASE WHEN YE.TERM_CODE ='5' THEN YE.WSUM_BAL   ELSE 0 END,0)    AS WSUM_5_10Y          --5至10年（含）-加权金额
        ,COALESCE(CASE WHEN YE.TERM_CODE ='6' THEN YE.ACTUAL_BAL ELSE 0 END,0)    AS BAL_10_999Y         --10年以上-贷款余额
        ,COALESCE(CASE WHEN YE.TERM_CODE ='6' THEN YE.WSUM_BAL   ELSE 0 END,0)    AS WSUM_10_999Y        --10年以上-加权金额
        ,COALESCE(YE.ACTUAL_BAL,0)                                                AS BAL_ALL             --所有期限-贷款余额
        ,COALESCE(YE.WSUM_BAL,0)                                                  AS WSUM_ALL            --所有期限-加权金额
        FROM (SELECT * FROM EDW.DS_LOAB_SUM WHERE ENTERPRISE_SCALE IN('CS03','CS04') AND DATA_DATE='20180331')YE
        -- 与地区表最细级关联
        LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON YE.FIN_ORG_NO=REAL.ORG_ID AND '20180331' BETWEEN REAL.START_DATE AND REAL.END_DATE
        LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '20180331' BETWEEN AREA.START_DATE AND AREA.END_DATE
    ) XY
    GROUP BY XY.FIN_ORG_DIST
            ,XY.CCY
            ,XY.ENTERPRISE_SCALE
            ,XY.SCALE_DESC;